use std::collections::HashMap;

use model_graph_types::connector::{Connector, ExecuteOptions};
use model_graph_types::datasource::{
    AttributeCondition, DeleteCondition, DeleteOptions, InsertOptions, QueryCondition,
    QueryExtention, UpdateCondition, UpdateConditionBuilder,
};

use model_graph_types::modeling::{
    system_meta_database, ClassGetter, DataSourceEntity, MetaConnector, PagingResponse,
    QueryConditionByClassOptionsBuilder,
};
use model_graph_types::{
    connector::QueryOptions,
    database::DataSource,
    datasource::{
        DataSourceConnector, QueryOptions as DataSourceConnectorQueryOptions, UpdateOptions,
    },
    modeling::{AttributeValueConvertor, Value},
};
use union_connector::UnionConnector;

use crate::common::SimpleAttributeValueConvertor;
use crate::generate_sql::{
    genertate_delete_by_keys_sql, genertate_delete_data_sql, genertate_insert_data_sql,
    genertate_query_one_sql, genertate_update_by_keys_sql, genertate_update_data_sql,
    genertate_where_by_keys_sql,
};
use crate::modeling_connector::SimpleMetaConnector;

#[derive(Debug, Default)]
pub struct SimpleDataSourceConnector {}

#[async_trait::async_trait]
impl DataSourceConnector for SimpleDataSourceConnector {
    async fn datasource(
        &self,
        meta_datasource: &Option<DataSource>,
        ds_id: u32,
    ) -> anyhow::Result<DataSource> {
        if ds_id == 0 {
            return system_meta_database();
        }
        if let Some(meta_ds) = meta_datasource {
            let entity: DataSourceEntity = self
                .instance_query_one(
                    &meta_datasource,
                    &meta_ds,
                    &DataSourceEntity::get_identifier()?,
                    QueryCondition::with_attribute(String::from("id"), ds_id.to_string())?,
                    None,
                    None,
                )
                .await?
                .try_into()?;
            entity.datasource()
        } else {
            let entity: DataSourceEntity = self
                .instance_query_one(
                    &meta_datasource,
                    &system_meta_database()?,
                    &DataSourceEntity::get_identifier()?,
                    QueryCondition::with_attribute(String::from("id"), ds_id.to_string())?,
                    None,
                    None,
                )
                .await?
                .try_into()?;
            entity.datasource()
        }
    }

    /// 从数据源中通过获取某个类型数据
    async fn instance_query_one_by_keys(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        keys: Vec<String>,
    ) -> anyhow::Result<Value> {
        let modeling = SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;
        let clazz = modeling.get_class(class_identifier).await?;
        //获取映射
        let mapping = modeling.get_class_persistence(class_identifier).await?;

        let sql = genertate_query_one_sql(
            &meta_datasource,
            datasource.id,
            &datasource.database_type,
            &clazz,
            &keys,
            mapping,
        )?;
        tracing::debug!(
            "SQL:{},{},{:?},{:?}",
            sql,
            class_identifier,
            keys,
            &datasource
        );
        //
        let connector = UnionConnector::open(&datasource).await?;
        let object = connector
            .query_object(
                &sql,
                &keys.iter().map(|v| Value::String(v.clone())).collect(),
                QueryOptions {},
            )
            .await?;
        tracing::debug!("查询结果:{:?}", object);
        Ok(object)
    }

    /// 从数据源中通过获取某个类型数据
    async fn instance_query_one(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        conditions: QueryCondition,
        extentions: Option<QueryExtention>,
        options: Option<DataSourceConnectorQueryOptions>,
    ) -> anyhow::Result<Value> {
        let mut result = self
            .instance_query(
                meta_datasource,
                datasource,
                class_identifier,
                conditions,
                extentions,
                options,
            )
            .await?;
        tracing::debug!("{:?}", result.data);
        if result.data.len() > 0 {
            return Ok(result
                .data
                .pop()
                .map_or(Err(anyhow::anyhow!("没有找到数据")), |v| Ok(v))?);
        }
        Err(anyhow::anyhow!("没有找到数据"))
    }

    /// 查询
    async fn instance_count(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        conditions: QueryCondition,
        options: Option<DataSourceConnectorQueryOptions>,
    ) -> anyhow::Result<u32> {
        let (_, count_sql, args) = self
            .instance_query_sql(meta_datasource, class_identifier, conditions, options)
            .await?;
        tracing::debug!("COUNT SQL:{}", count_sql);
        tracing::debug!("ARGS:{:?}", args);

        let connector = UnionConnector::open(&datasource).await?;
        let count_row: u32 = connector
            .query_one_value(&count_sql, &args, QueryOptions {})
            .await?
            .try_into()?;
        Ok(count_row)
    }

    /// 从数据源中通过获取某个类型数据
    async fn instance_query(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        conditions: QueryCondition,
        extentions: Option<QueryExtention>,
        options: Option<DataSourceConnectorQueryOptions>,
    ) -> anyhow::Result<PagingResponse<Vec<Value>>> {
        tracing::debug!(
            "[{}]conditions:{}",
            class_identifier,
            serde_json::to_string(&conditions)?
        );
        let (sql, count_sql, args) = self
            .instance_query_sql(meta_datasource, class_identifier, conditions, options)
            .await?;
        //
        tracing::debug!("COUNT SQL:{}", count_sql);
        tracing::debug!("SQL:{}", sql);
        tracing::debug!("ARGS:{:?}", args);

        let connector = UnionConnector::open(&datasource).await?;
        let count_row: u32 = connector
            .query_one_value(&count_sql, &args, QueryOptions {})
            .await?
            .try_into()?;
        let rows = connector
            .query_objects(&sql, &args, QueryOptions {})
            .await?;

        //附加信息处理
        let new_rows = if let Some(query_extention) = &extentions {
            let modeling = SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;
            let mut new_rows = vec![];

            for row in rows {
                match &row {
                    Value::Object(map) => {
                        //其他的class
                        let mut new_map = map.clone();
                        for (attr_key, (ext_class_identifier, c, e)) in
                            query_extention.class_selector.clone()
                        {
                            //
                            let relation_conditon = if let Some(c) = &c.relations {
                                if let Some(c1) = c.get(0) {
                                    Some(c1.conditions.clone())
                                } else {
                                    None
                                }
                            } else {
                                None
                            };
                            //根据关系添加查询条件, 暂时先实现一级关系的查询
                            let relation_condition = modeling
                                .query_condition_by_class_object(
                                    class_identifier,
                                    &ext_class_identifier,
                                    &row,
                                    &relation_conditon,
                                    QueryConditionByClassOptionsBuilder::default()
                                        .depth(1)
                                        .build()?,
                                )
                                .await?;
                            // 附加原有的查询条件
                            let mut condition = c.clone();
                            condition.relations = None;
                            condition.combine(&relation_condition)?;

                            tracing::error!("extention[{}]:{:?}", ext_class_identifier, condition);

                            //查询{ext_class_identifier}的类型的数据
                            let data = self
                                .instance_query(
                                    meta_datasource,
                                    datasource,
                                    &ext_class_identifier,
                                    condition,
                                    e,
                                    None,
                                )
                                .await?
                                .data;
                            if let Some(true) = query_extention.is_single {
                                //取其中一个或者None
                                if let Some(item) = data.get(0) {
                                    new_map.insert(attr_key.clone(), item.clone());
                                } else {
                                    new_map.insert(attr_key.clone(), Value::None);
                                }
                            } else {
                                new_map.insert(attr_key.clone(), Value::Array(data));
                            }
                        }
                        new_rows.push(Value::Object(new_map));
                    }
                    _ => {
                        new_rows.push(row);
                    }
                }
            }
            new_rows
        } else {
            rows
        };

        Ok(PagingResponse {
            total: count_row,
            data: new_rows,
        })
    }

    async fn instance_query_sql(
        &self,
        meta_datasource: &Option<DataSource>,
        class_identifier: &String,
        conditions: QueryCondition,
        options: Option<DataSourceConnectorQueryOptions>,
    ) -> anyhow::Result<(String, String, Vec<Value>)> {
        crate::query::instance_query(
            self,
            &meta_datasource,
            class_identifier,
            conditions,
            options,
        )
        .await
    }

    /// 从数据源中通过获取某个类型数据
    async fn instance_update_by_keys(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        keys: Vec<String>,
        value: HashMap<String, Value>,
        options: Option<UpdateOptions>,
    ) -> anyhow::Result<Value> {
        let modeling = SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;
        let clazz = modeling.get_class(class_identifier).await?;
        //获取映射
        let mapping = modeling.get_class_persistence(class_identifier).await?;

        let upsert = if let Some(options) = &options {
            let upsert = if let Some(upsert) = &options.upsert {
                upsert.clone()
            } else {
                false
            };
            let primary_key = if upsert {
                let primary_key = if let Some(primary_key) = &options.primary_key {
                    primary_key.clone()
                } else {
                    clazz.primary_key.clone()
                };
                primary_key
            } else {
                String::new()
            };
            (upsert, primary_key)
        } else {
            (false, String::new())
        };

        if upsert.0 {
            return self
                ._upsert(
                    meta_datasource,
                    datasource,
                    class_identifier,
                    &keys,
                    value,
                    &upsert.1,
                )
                .await;
        }

        let (sql, args) = genertate_update_by_keys_sql(
            meta_datasource,
            datasource.id,
            &datasource.database_type,
            &clazz,
            &keys,
            &upsert.1,
            value,
            mapping,
        )?;
        tracing::debug!("SQL:{}", sql);
        //
        let connector = UnionConnector::open(&datasource).await?;
        let object = connector
            .execute(
                &sql,
                &keys.iter().map(|v| Value::String(v.clone())).collect(),
                ExecuteOptions::new(),
            )
            .await?;
        tracing::debug!("查询结果:{:?}", object);
        Ok(Value::Number(object))
    }

    async fn instance_update(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        condition: UpdateCondition,
        value: HashMap<String, Value>,
        options: Option<UpdateOptions>,
    ) -> anyhow::Result<Value> {
        if let Some(ops) = &options {
            if let Some(upsert) = &ops.upsert {
                //主键
                if *upsert {
                    let primary_key = if let Some(pk) = &ops.primary_key {
                        pk.clone()
                    } else {
                        let modeling =
                            SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;

                        let clazz = modeling.get_class(class_identifier).await?;
                        clazz.primary_key.clone()
                    };
                    if let Some(keys) = value.get(&primary_key) {
                        let key_str = keys.try_into()?;
                        //
                        return self
                            ._upsert(
                                meta_datasource,
                                datasource,
                                class_identifier,
                                &vec![key_str],
                                value,
                                &primary_key,
                            )
                            .await;
                    }
                }
            }
        }

        let (sql, args) = genertate_update_data_sql(
            meta_datasource,
            datasource.id,
            &datasource.database_type,
            class_identifier,
            condition,
            value,
            options,
        )
        .await?;
        //
        tracing::debug!("SQL:{}", sql);
        tracing::debug!("ARGS:{:?}", args);

        let connector = UnionConnector::open(&datasource).await?;

        let rows = connector
            .execute(&sql, &args, ExecuteOptions::new())
            .await?;

        Ok(Value::Number(rows))
    }

    /// 从数据源中通过获取某个类型数据
    async fn instance_delete_by_keys(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        keys: Vec<String>,
        options: Option<DeleteOptions>,
    ) -> anyhow::Result<Value> {
        let modeling = SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;

        let clazz = modeling.get_class(class_identifier).await?;
        //获取映射
        let mapping = modeling.get_class_persistence(class_identifier).await?;

        let sql = genertate_delete_by_keys_sql(
            meta_datasource,
            &datasource.database_type,
            &clazz,
            &keys,
            mapping,
        )?;
        tracing::debug!("SQL:{}", sql);
        //
        let connector = UnionConnector::open(&datasource).await?;
        let object = connector
            .execute(
                &sql,
                &keys.iter().map(|v| Value::String(v.clone())).collect(),
                ExecuteOptions::new(),
            )
            .await?;
        tracing::debug!("查询结果:{:?}", object);
        Ok(Value::Number(object))
    }
    async fn instance_delete(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        conditions: DeleteCondition,
        options: Option<DeleteOptions>,
    ) -> anyhow::Result<Value> {
        let (sql, args) = genertate_delete_data_sql(
            meta_datasource,
            datasource.id,
            &datasource.database_type,
            class_identifier,
            conditions,
            options,
        )
        .await?;
        //
        tracing::debug!("SQL:{}", sql);
        tracing::debug!("ARGS:{:?}", args);

        let connector = UnionConnector::open(&datasource).await?;

        let rows = connector
            .execute(&sql, &args, ExecuteOptions::new())
            .await?;

        Ok(Value::Number(rows))
    }
    /// 从数据源中通过获取某个类型数据
    async fn instance_insert(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        values: HashMap<String, Value>,
        options: Option<InsertOptions>,
    ) -> anyhow::Result<Value> {
        if let Some(ops) = &options {
            if let Some(upsert) = &ops.upsert {
                //主键
                if *upsert {
                    let primary_key = if let Some(pk) = &ops.primary_key {
                        pk.clone()
                    } else {
                        let modeling =
                            SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;

                        let clazz = modeling.get_class(class_identifier).await?;
                        clazz.primary_key.clone()
                    };
                    if let Some(keys) = values.get(&primary_key) {
                        let key_str = keys.try_into()?;
                        //
                        return self
                            ._upsert(
                                meta_datasource,
                                datasource,
                                class_identifier,
                                &vec![key_str],
                                values,
                                &primary_key,
                            )
                            .await;
                    }
                }
            }
        }

        let (sql, args) = genertate_insert_data_sql(
            meta_datasource,
            &datasource.database_type,
            class_identifier,
            values,
            options,
        )
        .await?;
        //
        tracing::debug!("SQL:{}", sql);
        tracing::debug!("ARGS:{:?}", args);

        let connector = UnionConnector::open(&datasource).await?;

        let rows = connector
            .execute(&sql, &args, ExecuteOptions::new())
            .await?;

        Ok(Value::Number(rows))
    }
    // ---批量接口
}

impl SimpleDataSourceConnector {
    async fn _upsert(
        &self,
        meta_datasource: &Option<DataSource>,
        datasource: &DataSource,
        class_identifier: &String,
        keys: &Vec<String>,
        value: HashMap<String, Value>,
        primary_key: &String,
    ) -> anyhow::Result<Value> {
        let modeling = SimpleMetaConnector::new_with_meta_datasource(meta_datasource)?;

        let clazz = modeling.get_class(class_identifier).await?;
        //获取映射
        let mapping = modeling.get_class_persistence(class_identifier).await?;

        //查询是否存在
        let wheres =
            genertate_where_by_keys_sql(&datasource.database_type, &clazz, primary_key, keys)?;

        let connector = UnionConnector::open(&datasource).await?;

        let attribute_value_convertor = SimpleAttributeValueConvertor::default();
        let mut new_keys = vec![];
        for key in keys {
            let value = attribute_value_convertor
                .get_persistence_value(
                    class_identifier,
                    &primary_key,
                    &Value::String(key.clone()),
                    None,
                )
                .await?;
            new_keys.push(value);
        }
        let count_row: u32 = connector
            .query_one_value(
                &format!(
                    "SELECT COUNT(1) FROM {} WHERE {} ",
                    mapping.table_name, wheres
                ),
                &new_keys,
                QueryOptions {},
            )
            .await?
            .try_into()?;

        if count_row > 0 {
            //更新
            return self
                .instance_update(
                    meta_datasource,
                    datasource,
                    class_identifier,
                    UpdateConditionBuilder::default()
                        .attributes(vec![AttributeCondition::new_with_equal(
                            primary_key.clone(),
                            keys.join(","),
                        )])
                        .build()?,
                    value,
                    None,
                )
                .await;
        } else {
            //插入
            return self
                .instance_insert(meta_datasource, datasource, class_identifier, value, None)
                .await;
        }
    }
}
